﻿
Imports System.Data.SqlClient
Public Class ThanhVienDAO
    Public Shared Function GetAllThanhVienDAO() As DataTable
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSach_ThanhVien"
        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function

    Public Shared Function GetMaThanhVienDAO() As DataTable
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "DanhSachMa_ThanhVien"
        cmd.Connection = cn
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt As DataTable = New DataTable()
        da.Fill(dt)
        Return dt


    End Function

    Public Shared Sub InsertThanhVienDAO(ByVal ThanhVien As ThanhVienDTO)
        '  Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Insert_ThanhVien"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("username", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = ThanhVien.UserName
        cmd.Parameters.Add(para)

        para = New SqlParameter("pass", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = ThanhVien.Pass
        cmd.Parameters.Add(para)


        para = New SqlParameter("loai", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = ThanhVien.Loai
        cmd.Parameters.Add(para)


        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Shared Sub UpdateThanhVienDAO(ByVal ThanhVien As ThanhVienDTO)
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Update_ThanhVien"
        cmd.Connection = cn
        cmd.Connection.Open()



        Dim para As SqlParameter = New SqlParameter("username", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = ThanhVien.UserName
        cmd.Parameters.Add(para)

        para = New SqlParameter("pass", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = ThanhVien.Pass
        cmd.Parameters.Add(para)


        para = New SqlParameter("loai", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = ThanhVien.Loai
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub


    Public Shared Sub DeleteThanhVienDAO(ByVal username As String)

        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Delete_ThanhVien"
        cmd.Connection = cn
        cmd.Connection.Open()

        Dim para As SqlParameter = New SqlParameter("username", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = username
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub
    Public Shared Function KiemTraDangNhapDAO(ByVal user As String, ByVal pass As String) As Integer
        ' Dim sqlcn As SqlDataProvider = New SqlDataProvider()
        Dim cn As SqlConnection
        cn = SqlDataProvider.ConnectionData()
        Dim ds As ArrayList = New ArrayList()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "KiemTra_DangNhap"
        cmd.Connection = cn
        cmd.Connection.Open()
       

        Dim para As SqlParameter = New SqlParameter("username", SqlDbType.VarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = user
        cmd.Parameters.Add(para)

        para = New SqlParameter("pass", SqlDbType.NVarChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = pass
        cmd.Parameters.Add(para)

        Dim kq As Integer = cmd.ExecuteScalar()
        cmd.Connection.Close()
        Return kq

    End Function
End Class
